/**
 * @Description:
 * @author: Jiaming Zheng
 * @Date: Create in 16:50 2023/12/22
 * @Modified By:
 **/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.YearMonth;

public class Test {

//    public static void main(String[] args) {
//        String jdbcUrl = "jdbc:mysql://your_database_host:your_database_port/your_database_name";
//        String username = "your_username";
//        String password = "your_password";
//
//        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
//            calculateWorkHours(connection);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }
//
//    private static void calculateWorkHours(Connection connection) throws SQLException {
//        String sql = "SELECT name, process_id, manufact_start_date, IFNULL(manufact_end_date, NOW()) AS manufact_end_date, sum_work_hours FROM your_table_name";
//
//        try (PreparedStatement preparedStatement = connection.prepareStatement(sql);
//             ResultSet resultSet = preparedStatement.executeQuery()) {
//
//            while (resultSet.next()) {
//                String name = resultSet.getString("name");
//                LocalDate startDate = resultSet.getDate("manufact_start_date").toLocalDate();
//                LocalDate endDate = resultSet.getDate("manufact_end_date").toLocalDate();
//                double sumWorkHours = resultSet.getDouble("sum_work_hours");
//
//                calculateAndInsertMonthlyWorkHours(connection, name, startDate, endDate, sumWorkHours);
//            }
//        }
//    }
//
//    private static void calculateAndInsertMonthlyWorkHours(Connection connection, String name, LocalDate startDate, LocalDate endDate, double sumWorkHours) throws SQLException {
//        YearMonth startYearMonth = YearMonth.from(startDate);
//        YearMonth endYearMonth = YearMonth.from(endDate);
//
//        for (YearMonth currentYearMonth = startYearMonth; !currentYearMonth.isAfter(endYearMonth); currentYearMonth = currentYearMonth.plusMonths(1)) {
//            YearMonth lastDayOfMonth = currentYearMonth.atEndOfMonth();
//            LocalDate monthEndDate = endDate.isBefore(lastDayOfMonth) ? endDate : lastDayOfMonth;
//
//            double monthlyWorkHours = sumWorkHours * (double) monthEndDate.getDayOfMonth() / (double) lastDayOfMonth.getDayOfMonth();
//
//            // 插入或更新数据库中的工时数据，这里需要根据您的数据库结构进行调整
//            String insertSql = "INSERT INTO monthly_work_hours (name, year_month, work_hours) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE work_hours = ?";
//            try (PreparedStatement insertStatement = connection.prepareStatement(insertSql)) {
//                insertStatement.setString(1, name);
//                insertStatement.setString(2, currentYearMonth.toString());
//                insertStatement.setDouble(3, monthlyWorkHours);
//                insertStatement.setDouble(4, monthlyWorkHours);
//                insertStatement.executeUpdate();
//            }
//        }
//    }
}

